from flask import Flask, render_template, request, send_file
from sqlalchemy import create_engine, MetaData, Table, select
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import io

app = Flask(__name__)

# 数据库连接字符串
DATABASE_URI = 'mssql+pyodbc://username:password@server/database?driver=ODBC+Driver+17+for+SQL+Server'

# 创建数据库引擎
engine = create_engine(DATABASE_URI)
metadata = MetaData()

# 假设你的表名为 'images'，包含 'timestamp', 'site', 'image_data' 列11111
images_table = Table('images', metadata, autoload_with=engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()


@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        # 获取表单数据
        site = request.form['site']
        start_time = datetime.strptime(request.form['start_time'], '%Y-%m-%dT%H:%M')
        end_time = datetime.strptime(request.form['end_time'], '%Y-%m-%dT%H:%M')

        # 查询数据库
        query = select(images_table).where(
            images_table.c.site == site,
            images_table.c.timestamp >= start_time,
            images_table.c.timestamp <= end_time
        )
        result = session.execute(query).fetchall()

        # 将查询结果传递给模板
        return render_template('index.html', images=result)

    return render_template('index.html', images=None)


@app.route('/image/<int:image_id>')
def get_image(image_id):
    # 根据ID查询图片
    query = select(images_table).where(images_table.c.id == image_id)
    result = session.execute(query).fetchone()

    if result:
        # 返回图片
        return send_file(io.BytesIO(result.image_data), mimetype='image/jpeg')
    else:
        return "Image not found", 404


if __name__ == '__main__':
    app.run(debug=True)